{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-09-09T07:32:11.437892Z",
     "start_time": "2025-09-09T07:32:10.435467Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "path = 'D:/2506A/monty03/day12/file/'"
   ],
   "outputs": [],
   "execution_count": 2
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# pandas读取文件内容",
   "id": "4ce50d615b01716e"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 1. 读取csv文件",
   "id": "95ab184cfc38eef4"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:36:58.309604Z",
     "start_time": "2025-09-09T07:36:58.302550Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 1. 读取数据\n",
    "data = pd.read_csv(path + '学生信息.csv')\n",
    "# print(data)\n",
    "\n",
    "# 2. 查看前几行\n",
    "# print(data.head()) # 默认显示5行\n",
    "print(data.head(3))\n",
    "\n",
    "\n",
    "# 3.查看数据的形状(几行几列)\n",
    "print(data.shape)\n",
    "\n",
    "# 4. 查看表的列名(表头)\n",
    "print(data.columns)\n",
    "\n",
    "# 5. 查看索引\n",
    "print(data.index)\n",
    "\n",
    "# 6. 查看每一列的数据类型\n",
    "print(data.dtypes)"
   ],
   "id": "cd7ccd70b3da4594",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    姓名  年龄      地址\n",
      "0  聂茹凤  21  吉林省松原市\n",
      "1  谭鑫宇  22  吉林省松原市\n",
      "2  韩耀祖  23  河北省邯郸市\n",
      "(6, 3)\n",
      "Index(['姓名', '年龄', '地址'], dtype='object')\n",
      "RangeIndex(start=0, stop=6, step=1)\n",
      "姓名    object\n",
      "年龄     int64\n",
      "地址    object\n",
      "dtype: object\n"
     ]
    }
   ],
   "execution_count": 10
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 2. 读取自定义的CSV数据类型",
   "id": "f43e4b0e1bed1eb7"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:41:53.381361Z",
     "start_time": "2025-09-09T07:41:53.375285Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 读取CSV文件并设置索引列\n",
    "data = pd.read_csv(path + '自定义数据.csv',names=['性别','姓名','年龄','电话','派别','出生日期'],index_col='出生日期')\n",
    "\n",
    "print(data)"
   ],
   "id": "5f4686313f68a5f3",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         性别   姓名  年龄           电话     派别\n",
      "出生日期                                    \n",
      "2000/1/1  男   杨过  19  13901234567  终南山古墓\n",
      "2000/1/2  女  小龙女  25  13801111111  终南山古墓\n",
      "2020/1/1  男   郭靖  40  13705555555   湖北襄阳\n",
      "2000/1/4  女   黄蓉  35  13601111111   湖北襄阳\n",
      "2000/1/5  男  张无忌  18  13506666666     明教\n",
      "2000/1/6  女  周芷若  17  13311111111     明教\n",
      "2000/1/7  女   赵敏  17  18800000000     明教\n"
     ]
    }
   ],
   "execution_count": 14
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 3. txt文件转换成csv文件",
   "id": "2fba252a497d1ea2"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:45:23.958160Z",
     "start_time": "2025-09-09T07:45:23.949993Z"
    }
   },
   "cell_type": "code",
   "source": [
    "data  = pd.read_csv(path + '自定义数据2.txt')\n",
    "data.to_csv(path + '自定义数据3.csv')\n",
    "print('转换完成')"
   ],
   "id": "8b39bf076280b535",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "转换完成\n"
     ]
    }
   ],
   "execution_count": 17
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 4. 读取MySQL中的内容",
   "id": "3fa24b5c695b0f13"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:49:32.055571Z",
     "start_time": "2025-09-09T07:49:31.846484Z"
    }
   },
   "cell_type": "code",
   "source": [
    "from sqlalchemy import create_engine\n",
    "# 1. 准备一个连接字符串\n",
    "conn_str = 'mysql+pymysql://root:root@127.0.0.1:3306/db05'\n",
    "\n",
    "# 2. 创建数据库引擎\n",
    "enumerate = create_engine(conn_str)\n",
    "\n",
    "data = pd.read_sql('select * from student_table',con=enumerate)\n",
    "print(data)"
   ],
   "id": "ddaa0a699f6f4ea3",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    姓名  年龄      地址\n",
      "0  刘千琪  21  吉林省松原市\n",
      "1  崔龙腾  22  吉林省松原市\n",
      "2  李欣桐  23  河北省邯郸市\n"
     ]
    }
   ],
   "execution_count": 18
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T07:51:05.154936Z",
     "start_time": "2025-09-09T07:51:05.145987Z"
    }
   },
   "cell_type": "code",
   "source": [
    "sql = 'select * from student_table where 年龄<= 21'\n",
    "data = pd.read_sql(sql,con=enumerate)\n",
    "print(data)"
   ],
   "id": "882935c767c45ff3",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    姓名  年龄      地址\n",
      "0  刘千琪  21  吉林省松原市\n"
     ]
    }
   ],
   "execution_count": 20
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T08:00:34.156640Z",
     "start_time": "2025-09-09T08:00:34.148296Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 参数化查询，防止SQL注入\n",
    "sql = 'select * from student_table where 年龄 <= %s and 姓名 = %s'\n",
    "params = (21,'刘千琪')\n",
    "data = pd.read_sql(sql,con=enumerate,params=params)\n",
    "print(data)"
   ],
   "id": "f4c550e8ae3049c",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    姓名  年龄      地址\n",
      "0  刘千琪  21  吉林省松原市\n"
     ]
    }
   ],
   "execution_count": 21
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 5. 读取Excel",
   "id": "af29ab7fdf6f9d06"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T08:08:43.748909Z",
     "start_time": "2025-09-09T08:08:43.739035Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 将 Excel 文件的第一行（即第 0 行，索引从 0 开始）,如果是None，那么表头的索引就是0，第一行数据的索引就是1\n",
    "data = pd.read_excel(path + '学生信息4.xlsx',header=None)\n",
    "\n",
    "# 自定义表头\n",
    "# data.columns = ['Name','Age','Address']\n",
    "\n",
    "\n",
    "print(data)\n",
    "# # 查看各种信息\n",
    "# print(data.head())      # 前5行数据\n",
    "# print(data.shape)       # 数据形状（行数，列数）\n",
    "# print(data.columns)     # 列名列表\n",
    "# print(data.index)       # 索引信息\n",
    "# print(data.dtypes)      # 各列数据类型"
   ],
   "id": "51c59946a9f7db75",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     0    1       2\n",
      "0   姓名   年龄      地址\n",
      "1  聂茹凤   21  吉林省松原市\n",
      "2  谭鑫宇   22  吉林省松原市\n",
      "3  韩耀祖   23  河北省邯郸市\n",
      "4  NaN  NaN     NaN\n",
      "5  刘千琪   21  吉林省松原市\n",
      "6  崔龙腾   22  吉林省松原市\n",
      "7  李欣桐   23  河北省邯郸市\n"
     ]
    }
   ],
   "execution_count": 36
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 6. 读取多个工作表",
   "id": "e634c85b792d9366"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T08:26:03.289857Z",
     "start_time": "2025-09-09T08:26:03.263439Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 读取指定的工作表\n",
    "df_sheet1 = pd.read_excel(path + '多工作表.xlsx',sheet_name='Sheet1')\n",
    "# print(df_sheet1)\n",
    "\n",
    "# 读取所有的工作表\n",
    "all_sheets = pd.read_excel(path + '多工作表.xlsx',sheet_name=None)\n",
    "for sheet_name,df in all_sheets.items():\n",
    "    print(sheet_name)\n",
    "    print(df)\n",
    "print('=' * 30)\n",
    "\n",
    "sheets = pd.read_excel(path + '多工作表.xlsx',sheet_name=['Sheet1','Sheet2'])\n",
    "for sheet_name,df in sheets.items():\n",
    "    print(sheet_name)\n",
    "    print(df)"
   ],
   "id": "54f1cc796a497246",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sheet1\n",
      "   id    姓名\n",
      "0   1   刘亦菲\n",
      "1   2   王丽坤\n",
      "2   3  迪丽热巴\n",
      "Sheet2\n",
      "    姓名  年龄      地址\n",
      "0  聂茹凤  21  吉林省松原市\n",
      "1  谭鑫宇  22  吉林省松原市\n",
      "2  韩耀祖  23  河北省邯郸市\n",
      "==============================\n",
      "Sheet1\n",
      "   id    姓名\n",
      "0   1   刘亦菲\n",
      "1   2   王丽坤\n",
      "2   3  迪丽热巴\n",
      "Sheet2\n",
      "    姓名  年龄      地址\n",
      "0  聂茹凤  21  吉林省松原市\n",
      "1  谭鑫宇  22  吉林省松原市\n",
      "2  韩耀祖  23  河北省邯郸市\n"
     ]
    }
   ],
   "execution_count": 44
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-09T08:26:59.805138Z",
     "start_time": "2025-09-09T08:26:59.796935Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 读取JSON\n",
    "df = pd.read_json(path + 'stu.json')\n",
    "print(df)"
   ],
   "id": "cb2d3d5b45821ab6",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    姓名  年龄      地址\n",
      "0  刘千琪  21  吉林省松原市\n",
      "1  崔龙腾  22  吉林省松原市\n",
      "2  李欣桐  23  河北省邯郸市\n"
     ]
    }
   ],
   "execution_count": 46
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
